﻿using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Collections;
using umbraco.DataLayer;
using umbraco.BusinessLogic;
using umbraco.editorControls;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Web.SessionState;
using System.Reflection;
using umbraco.NodeFactory;
using ExtendedWorkFlow.Commands;

namespace ExtendedWorkFlow.Helpers
{
    public class WorkFlowHelper
    {

        #region Sql Helper

        /// <summary>
        /// Current sqlhelper
        /// </summary>
        static ISqlHelper SqlHelper
        {
            get { return umbraco.BusinessLogic.Application.SqlHelper; }
        }

        #endregion

        /// <summary>
        /// Load assembly from namespace and type
        /// </summary>
        /// <param name="Type">Namespace and assembly to load example ExtendedWorkFlow.Commands.Publish, ExtendedWorkFlow</param>        
        /// <returns>IExtendedWorkFlowCommand loaded from Type</returns>
        public static IExtendedWorkFlowCommand GetRunCommand(string Type)
        {
            try
            {
                string[] arrNamespace = Type.Split(",".ToCharArray());

                // Check namespace and assembly exist
                if (arrNamespace.GetUpperBound(0) == 1)
                {
                    Assembly objAssembly = Assembly.Load(arrNamespace[1].Trim());

                    if (objAssembly != null)
                    {
                        foreach (Type type in objAssembly.GetTypes())
                        {
                            if (type.FullName == arrNamespace[0] &&
                                ImplementsInterfaceIExtendedWorkFlowCommand(type))
                            {
                                // Interface supported and type found create new instance of it
                                object objRunCommand = Activator.CreateInstance(type);

                                // return loaded method
                                return objRunCommand as IExtendedWorkFlowCommand;
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                System.Diagnostics.Debug.WriteLine(ex.ToString());
            }

            return null;
        }

        /// <summary>
        /// Check all interfaces on type to see if it support IExtendedWorkFlowCommand
        /// </summary>
        /// <param name="Type">Type to check</param>
        /// <returns>Bool to indicate if type implements interface</returns>
        private static bool ImplementsInterfaceIExtendedWorkFlowCommand(Type Type)
        {
            foreach (Type objType in Type.GetInterfaces())
                if (typeof(IExtendedWorkFlowCommand).IsAssignableFrom(objType))
                    return true;

            return false;
        }

        /// <summary>
        /// Get all users and roles
        /// </summary>
        /// <returns>IRecordsReader containing all users and roles</returns>
        public static IRecordsReader GetUserRoles()
        {
            string strSql = @"
                select  convert(varchar(5), umbracoUserType.id) + ':t' as [uid], umbracoUserType.id, 't' as [type], umbracoUserType.userTypeAlias + '(type)' as [name]
                from    umbracoUserType
                UNION ALL
                select  convert(varchar(5), umbracoUser.id) + ':u' as [uid], umbracoUser.id, 'u', umbracoUser.userName
                from    umbracoUser";

            return SqlHelper.ExecuteReader(strSql, new IParameter[] { });
        }

        /// <summary>
        /// Get all users and roles
        /// </summary>
        /// <returns>IRecordsReader containing all users and roles</returns>
        public static IRecordsReader GetUserRoles(int NodeId)
        {
            string strSql = @"
                select	convert(varchar(5), umbracoUserType.id) + ':t' as [uid], umbracoUserType.id, 't' as [type], umbracoUserType.userTypeAlias + '(type)' as [name]
                from	cmsWorkFlowUserRoles
		                inner join umbracoUserType on umbracoUserType.id = cmsWorkFlowUserRoles.id and cmsWorkFlowUserRoles.type = 't'
                where	cmsWorkFlowUserRoles.nodeid = @nodeid
                UNION ALL
                select	convert(varchar(5), umbracoUser.id) + ':u' as [uid], umbracoUser.id, 'u', umbracoUser.userName
                from	cmsWorkFlowUserRoles
		                inner join umbracoUser on umbracoUser.id = cmsWorkFlowUserRoles.id and cmsWorkFlowUserRoles.type = 'u'
                where	cmsWorkFlowUserRoles.nodeid = @nodeid";

            return SqlHelper.ExecuteReader(strSql, new IParameter[] { SqlHelper.CreateParameter("@nodeid", NodeId) });
        }

        /// <summary>
        /// Remove all users and roles from a node
        /// </summary>
        /// <param name="NodeId">Node to remove from</param>
        public static void RemoveUserRolesFromNode(int NodeId)
        {
            string strSql = "delete from cmsWorkFlowUserRoles where nodeid = @nodeid";
            SqlHelper.ExecuteNonQuery(strSql, new IParameter[] { 
                SqlHelper.CreateParameter("@nodeid", NodeId) });
        }

        /// <summary>
        /// Add user or role to node
        /// </summary>
        /// <param name="NodeId">Node to associate with</param>
        /// <param name="Id">Id of the user or role</param>
        /// <param name="Type">Type user/role</param>
        public static void AddUserRoleToNode(int NodeId, int Id, string Type)
        {
            string strSql = "insert into cmsWorkFlowUserRoles (id, nodeid, type) values (@id, @nodeid, @type)";
            SqlHelper.ExecuteNonQuery(strSql, new IParameter[] { 
                SqlHelper.CreateParameter("@id", Id), 
                SqlHelper.CreateParameter("@nodeid", NodeId), 
                SqlHelper.CreateParameter("@type", Type) });
        }

        /// <summary>
        /// Remove user or role from node
        /// </summary>
        /// <param name="NodeId">Node to remove from</param>
        /// <param name="Id">Id of the user or role</param>
        /// <param name="Type">Type user/role</param>
        public static void RemoveUserRoleFromNode(int NodeId, int Id, string Type)
        {
            string strSql = "delete from cmsWorkFlowUserRoles where [id] = @id and [nodeid] = @nodeid and [type] = @type";
            SqlHelper.ExecuteNonQuery(strSql, new IParameter[] { 
                SqlHelper.CreateParameter("@id", Id), 
                SqlHelper.CreateParameter("@nodeid", NodeId), 
                SqlHelper.CreateParameter("@type", Type) });
        }

        /// <summary>
        /// Get a users workbox
        /// </summary>
        /// <param name="User">User to lookup</param>
        /// <returns>DataSet containing States available and available commands</returns>
        public static DataSet GetUserWorkBox(umbraco.BusinessLogic.User User)
        {
            DataSet dtsDataSet = new DataSet();
            dtsDataSet.Tables.Add(GetUserWorkBoxStates(User));
            dtsDataSet.Tables.Add(GetUserWorkBoxActions(User));

            return dtsDataSet;
        }

        /// <summary>
        /// Get a users workbox of document states
        /// </summary>
        /// <param name="User">User to lookup</param>
        /// <returns>IRecordsReader</returns>
        public static DataTable GetUserWorkBoxStates(umbraco.BusinessLogic.User User)
        {
            DataTable tblWorkBox = new DataTable();
            tblWorkBox.Columns.Add("id", typeof(int));
            tblWorkBox.Columns.Add("documentid", typeof(int));
            tblWorkBox.Columns.Add("groupid", typeof(int));
            tblWorkBox.Columns.Add("stateid", typeof(int));
            tblWorkBox.Columns.Add("name", typeof(string));
            tblWorkBox.Columns.Add("createDate", typeof(DateTime));
            tblWorkBox.Columns.Add("updateDate", typeof(DateTime));
            tblWorkBox.Columns.Add("userId", typeof(int));
            tblWorkBox.Columns.Add("userName", typeof(string));

            string strSql = @"
            select
                cmsWorkFlow.id,
                cmsWorkFlow.documentid,
                cmsWorkFlow.groupid,
                cmsWorkFlow.stateid,
                cmsDocument.text as [name],
                cmsWorkFlow.updateDate,
                cmsWorkFlow.createDate,
                editor.id as [userId],
                editor.userName
            from
                umbracoUser
                CROSS JOIN
                cmsWorkFlow cmsWorkFlow
                inner join cmsDocument on cmsDocument.nodeId = cmsWorkFlow.documentid and cmsDocument.newest = 1
                inner join cmsWorkFlowUserRoles StatePermissions on 
                        StatePermissions.nodeId = cmsWorkFlow.stateid
                    and	(
		                        StatePermissions.id = umbracoUser.id and StatePermissions.type = 'u'
	                        OR	StatePermissions.id = umbracoUser.userType and StatePermissions.type = 't'
                        )
                inner join umbracoUser editor on editor.id = cmsWorkFlow.userid
            where
                    umbracouser.id = @id
                and cmsWorkFlow.status = 0
            order by
                groupid,
                stateid";

            using (IRecordsReader objReader = SqlHelper.ExecuteReader(strSql, new IParameter[] { 
                SqlHelper.CreateParameter("@id", User.Id) }))
            {
                while (objReader.Read())
                {
                    DataRow rowRow = tblWorkBox.NewRow();
                    rowRow["id"] = objReader.GetInt("id");
                    rowRow["documentid"] = objReader.GetInt("documentid");
                    rowRow["groupid"] = objReader.GetInt("groupid");
                    rowRow["stateid"] = objReader.GetInt("stateid");
                    rowRow["name"] = objReader.GetString("name");
                    rowRow["updateDate"] = objReader.GetDateTime("updateDate");
                    rowRow["createDate"] = objReader.GetDateTime("createDate");
                    rowRow["userId"] = objReader.GetInt("userId");
                    rowRow["userName"] = objReader.GetString("userName");
                    tblWorkBox.Rows.Add(rowRow);
                }
            }

            // Make sure the object is disposed of correctly
            return tblWorkBox;
        }

        /// <summary>
        /// Get a users workbox of available actions
        /// </summary>
        /// <param name="User">User to lookup</param>
        /// <returns>IRecordsReader</returns>
        public static DataTable GetUserWorkBoxActions(umbraco.BusinessLogic.User User)
        {
            DataTable tblWorkBox = new DataTable();
            tblWorkBox.Columns.Add("id", typeof(int));
            tblWorkBox.Columns.Add("documentid", typeof(int));
            tblWorkBox.Columns.Add("actionid", typeof(int));

            string strSql = @"
            select distinct
                cmsWorkFlow.id,
                cmsWorkFlow.documentid,    
                actionNodes.id as [actionid]
            from
	            umbracoUser
	            CROSS JOIN
                cmsWorkFlow cmsWorkFlow
                inner join cmsDocument on cmsDocument.nodeId = cmsWorkFlow.documentid and cmsDocument.newest = 1
                inner join cmsWorkFlowUserRoles StatePermissions on 
			            StatePermissions.nodeId = cmsWorkFlow.stateid
		            and	(
					            StatePermissions.id = umbracoUser.id and StatePermissions.type = 'u'
				            OR	StatePermissions.id = umbracoUser.userType and StatePermissions.type = 't'
			            )       
                inner join umbracoNode actionNodes on actionNodes.parentID = cmsWorkFlow.stateid
                inner join cmsWorkFlowUserRoles ActionPermissions on 
			            ActionPermissions.nodeId = actionNodes.id
		            and	(
					            ActionPermissions.id = umbracoUser.id and ActionPermissions.type = 'u'
				            OR	ActionPermissions.id = umbracoUser.userType and ActionPermissions.type = 't'
			            )
    
            where
		            umbracouser.id = @id
	            and cmsWorkFlow.status = 0";

            using (IRecordsReader objReader = SqlHelper.ExecuteReader(strSql, new IParameter[] { 
                SqlHelper.CreateParameter("@id", User.Id) }))
            {
                while (objReader.Read())
                {
                    DataRow rowRow = tblWorkBox.NewRow();
                    rowRow["id"] = objReader.GetInt("id");
                    rowRow["documentid"] = objReader.GetInt("documentid");
                    rowRow["actionid"] = objReader.GetInt("actionid");
                    tblWorkBox.Rows.Add(rowRow);
                }
            }

            // Make sure the object is disposed of correctly
            return tblWorkBox;
        }

    }
}
